const express = require('express');
const path = require('path')
const mysql = require('mysql')
const moment = require('moment')
const fs = require('fs')
const { getUnixTime } = require('./util/tool.js')

const artTemplate = require('art-template');
const express_template = require('express-art-template');


const app = express();



//设置托管静态资源中间件
app.use('/uploads', express.static(__dirname + '/uploads'));
app.use('/static', express.static(__dirname + '/static'));

var multer = require('multer')
//设置上传的目录
var upload = multer({ dest: 'uploads/' })
// const { CLIENT_RENEG_WINDOW } = require('tls');




app.use(express.json()) // for parsing application/json
app.use(express.urlencoded({ extended: true })) // for parsing application/x-www-form-urlencoded

//配置模板的路径
app.set('views', __dirname + '/views/');
//设置express_template模板后缀为.html的文件(不设这句话，模板文件的后缀默认是.art)
app.engine('html', express_template);
//设置视图引擎为上面的html
app.set('view engine', 'html');

//定义一个过滤器dateFormat
artTemplate.defaults.imports.dateFormat = function (time, format = 'YYYY-MM-DD HH:mm:ss') {
    return moment.unix(time).format(format);
}

// 连接数据库
let dbConfig = require('./config/dbconfig.js')
var connection = mysql.createConnection(dbConfig);

//连接mysql1
connection.connect(function (err) {
    if (err) {
        throw err;
    }
    console.log('成功射死');
});
// 只要是一种映射的数据关系，都可以用对象形式来简化if elseif
let statusTextMap = {
    0: '<span class="c-gray">待审核</span>',
    1: '<span class="c-green">审核通过</span>',
    2: '<span class="c-red">审核失败</span>'
}


//导入路由
const router = require('./router/router.js');
app.use(router);


// 文章列表
app.get('/', (req, res) => {
    // 1. 编写sql语句
    let sql = `select t1.*,t2.cate_name from masterpieces1  as t1 
                left join category t2 on t1.cat_id = t2.cate_id 
                where t1.Audit_status = 1 and t1.is_delete = 0 order by t1.id desc`;
    // 2. 执行sql
    connection.query(sql, (err, rows) => {
        console.log('异步回调')
        console.log(rows); // [{title,status},{}]
        let data = rows.map((item) => {
            item.Audit_status_text = statusTextMap[item.Audit_status]
            return item;
        })
        console.log(data)
        // 3. 把查询出来的数据分配到模板引擎中
        res.render('index.html', { articles: data })
    })

})
// 文章回收站列表
app.get('/recyclelist', (req, res) => {
    // 1. 编写sql语句
    let sql = `select t1.*,t2.cate_name from masterpieces1  as t1 
                left join category t2 on t1.cat_id = t2.cate_id 
                where t1.Audit_status = 1 and t1.is_delete = 1 order by t1.id desc`;
    // 2. 执行sql
    connection.query(sql, (err, rows) => {
        console.log('异步回调')
        console.log(rows); // [{title,status},{}]
        let data = rows.map((item) => {
            item.Audit_status_text = statusTextMap[item.Audit_status]
            return item;
        })
        console.log(data)
        // 3. 把查询出来的数据分配到模板引擎中
        res.render('recycle.html', { articles: data })
    })

})
// 文章删除
app.get('/delete', (req, res) => {
    //1. 接受要删除的文章的id
    let { id } = req.query;
    //2. 编写sql语句，删除
    let sql = `delete from masterpieces1 where id = ${id}`;
    connection.query(sql, (err, result) => {
        // 3.判断结果
        if (result.affectedRows) {
            // 删除成功 ，重定向到首页
            res.redirect('/')
        } else {
            // 删除失败，响应js代码，让浏览器执行
            res.send("<script>alert('删除失败')</script>; location.href = '/'; ")
        }
    })

})
// 添加文章的表单页面
app.get('/add', (req, res) => {
    // 取出所有的分类数据分配到模板中
    let sql = "select * from category";
    connection.query(sql, (err, rows) => {
        res.render('add.html', { cats: rows })
    })
})
// 实现数据添加入库
app.post('/insert', upload.single('img'), (req, res) => {
    console.log('req.body:', req.body)
    console.log('req.file:', req.file)
    //判断是否有图片
    let imgPath = '';
    if (req.file) {
        let { originalname, filename, destination } = req.file
        let ext = originalname.substring(originalname.indexOf('.'));
        //把上传成功的文件进行重命名 
        let oldPath = path.join(__dirname, destination, filename);
        let newPath = path.join(__dirname, destination, filename) + ext;
        //记录数据库存放的路径 upload/....png
        imgPath = `${destination}${filename}${ext}`
        fs.renameSync(oldPath, newPath)
    }
    let { article, author, cat_id, content, Audit_status } = req.body
    let sql = 'insert into masterpieces1(article,author,cat_id,content,img,Audit_status,addtime)values(?,?,?,?,?,?,?)';
    let nowTime = getUnixTime();
    let bind = [article, author, cat_id, content, imgPath, Audit_status, nowTime]
    console.log(sql, bind);
    connection.query(sql, bind, (err, result) => {
        if (result.affectedRows) {
            res.send("<script>alert('成功');location.href='/';</script>")
        } else {
            res.send("<script>alert('失败');location.href='/add';</script>")
        }
    })
})
//实现编辑文章的回显操作
app.get('/edit', (req, res) => {
    //接收参数
    let { id } = req.query;
    //编写sql语句 查询当前文章数据分配给模板
    let sql1 = `select * from masterpieces1 where id = ${id}`;
    connection.query(sql1, (err, rows1) => {
        console.log(rows1)
        let sql2 = "select * from category";
        connection.query(sql2, (err, rows2) => {
            res.render('edit.html', {

                masterpieces1: rows1[0],
                cats: rows2
            })
        })

    })


})
//实现文章更新入库操作
app.post('/update', (req, res) => {
    let { id, author, article, Audit_status, content, cat_id } = req.body
    let update_time = getUnixTime();
    let bind = [author, article, Audit_status, content, cat_id, update_time, id]
    let sql = `update masterpieces1 set author = ?,article = ?,Audit_status=?,content=?,cat_id=?,addtime=? where id =?`
    connection.query(sql, bind, (err, result) => {
        if (result.affectedRows) {
            res.redirect('/')

        } else {
            res.send('<script>alert("编辑失败");location.href="/";</script>')
        }
    })
})
//实现文章加入回收站
app.get('/recycle', (req, res) => {
    let { id = 0 } = req.query;
    console.log(id)
    let sql = `update masterpieces1 set is_delete = 1 where id = ${id}`;
    connection.query(sql, (err, result) => {
        let { affectedRows } = result;
        if (affectedRows) {

            res.redirect('/')
        } else {
            res.send("<script>alert('加入失败');location.herf='/';</script>")
        }
    })
})
//实现文章还原
app.get('/restore', (req, res) => {
    let { id = 0 } = req.query;
    console.log(id)
    let sql = `update masterpieces1 set is_delete = 0 where id = ${id}`;
    connection.query(sql, (err, result) => {
        let { affectedRows } = result;
        if (affectedRows) {
            res.redirect('/')
        } else {
            res.send("<script>alert('还原失败');location.herf='/';</script>")
        }
    })
})

//展示一个上传文件的表单
app.get('/addImg', (req, res) => {
    res.render('addimg.html')
})
//处理单文件上传
app.post('/upload', upload.single('photo'), (req, res) => {
    console.log(req.file);//接收二进制数据
    let { originalname, filename, destination } = req.file

    let ext = originalname.substring(originalname.indexOf('.'));
    //把上传成功的文件进行重命名
    let oldPath = path.join(__dirname, destination, filename);
    let newPath = path.join(__dirname, destination, filename) + ext;

    fs.renameSync(oldPath, newPath)
    console.log(req.body);
    res.send('upload success')
})

app.listen(8800, () => {
    console.log('射死犯贱奥运小日本')
})